#!/usr/bin/env python
# coding=utf-8

import MySQLdb
import time
import os
import json

def function_sdnlog_input():
    # 连接数据库
    conn = MySQLdb.connect(
                    host='localhost',
                    port=3306,
                    user='root',
                    passwd='cnv6201',
                    db='PRESENTATION',
                    charset='utf8',
                )
    cur = conn.cursor()

    # 清空数据库中表格内的信息
    cur.execute("delete from presentation_sdn_log")

    # 打开SDN监控扫描日志的JSON文件读取数据
    with open(os.getcwd() + '/JsonFileLog/log4icmp.json', "r", encoding='UTF-8') as f:
        data = json.load(f)

    # remove duplicates in data['layer_info']
    log_set = set()
    for i in range(len(data['layer_info'])):
        layer = data['layer']
        src_ip = data['layer_info'][i]['src_ip']
        dst_ip = data['layer_info'][i]['dst_ip']
        result = data['layer_info'][i]['result']
        time = data['layer_info'][i]['time']
        log_set.add((layer,src_ip,dst_ip,result,time))

    # sort by time
    log_list = list(log_set)
    log_list.sort(key= lambda x:x[4])

    # read log_list and write it into database
    for obj in log_list:
        if isinstance(obj, tuple):
            layer = obj[0]
            src_ip = obj[1]
            dst_ip = obj[2]
            result = obj[3]
            time = obj[4]
            cur.execute(
            "insert into presentation_sdn_log(layer, src_ip, dst_ip, result, time) values(%s,%s,%s,%s,%s)",
            [layer, src_ip, dst_ip, result, time])
    f.close()




    with open(os.getcwd() + '/JsonFileLog/log4arp.json', "r", encoding='UTF-8') as d:
        data = json.load(d)

    # remove duplicates in data['layer_info']
    log_set = set()
    for i in range(len(data['layer_info'])):
        layer = data['layer']
        src_ip = data['layer_info'][i]['src_ip']
        dst_ip = data['layer_info'][i]['dst_ip']
        result = data['layer_info'][i]['result']
        time = data['layer_info'][i]['time']
        log_set.add((layer,src_ip,dst_ip,result,time))

    # sort by time
    log_list = list(log_set)
    log_list.sort(key= lambda x:x[4])

    # read log_list and write it into database
    for obj in log_list:
        if isinstance(obj, tuple):
            layer = obj[0]
            src_ip = obj[1]
            dst_ip = obj[2]
            result = obj[3]
            time = obj[4]
            cur.execute(
            "insert into presentation_sdn_log(layer, src_ip, dst_ip, result, time) values(%s,%s,%s,%s,%s)",
            [layer, src_ip, dst_ip, result, time])
    d.close()


    with open(os.getcwd() + '/JsonFileLog/log4tcp.json', "r", encoding='UTF-8') as d:
        data = json.load(d)

    # remove duplicates in data['layer_info']
    log_set = set()
    for i in range(len(data['layer_info'])):
        layer = data['layer']
        src_ip = data['layer_info'][i]['client_ip']
        dst_ip = data['layer_info'][i]['server_ip']
        result = data['layer_info'][i]['result']
        time = data['layer_info'][i]['time']
        log_set.add((layer,src_ip,dst_ip,result,time))

    # sort by time
    log_list = list(log_set)
    log_list.sort(key= lambda x:x[4])

    # read log_list and write it into database
    for obj in log_list:
        if isinstance(obj, tuple):
            layer = obj[0]
            src_ip = obj[1]
            dst_ip = obj[2]
            result = obj[3]
            time = obj[4]
            cur.execute(
            "insert into presentation_sdn_log(layer, src_ip, dst_ip, result, time) values(%s,%s,%s,%s,%s)",
            [layer, src_ip, dst_ip, result, time])
    d.close()

    cur.close()
    conn.commit()
    conn.close()
    print("扫描行为数据存储成功")
